/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.sqlite;

import com.dao.access.GenericDAO;
import com.dao.access.GenericDAOImp;
import com.healthmarketscience.jackcess.Column;
import com.healthmarketscience.jackcess.Cursor;
import com.healthmarketscience.jackcess.CursorBuilder;
import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.DatabaseBuilder;
import com.healthmarketscience.jackcess.Row;
import com.healthmarketscience.jackcess.Table;
import com.model.access.*;
import java.io.File;
import java.io.IOException;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import syncmellariusnote.SyncMellariusNot;
import syncmellariusnote.helpers.AccessObjectRepo;
import syncmellariusnote.helpers.RepoHelper;

/**
 *
 * @author Vitor
 */
public class SQLiteJDBC {

    Connection c;
    GenericDAO genericDAO;
    Database dbAccess;
    String stringConnection;

    public SQLiteJDBC(String direc) throws SQLException, ClassNotFoundException, IOException {
        Class.forName("org.sqlite.JDBC");
        c = null;
        dbAccess = DatabaseBuilder.open(new File(SyncMellariusNot.getPath()));
        //dbAccess = DatabaseBuilder.open(new File("c:/MD/dados_apis.mdb"));
        String escaped = direc.replace("\\", "\\\\");
        this.stringConnection = "jdbc:sqlite:" + escaped;
        //stringConnection = "jdbc:sqlite:\\C:\\Users\\Vitor\\Documents\\NetBeansProjects\\SyncMellariusNote\\dist\\template\\mellariusBd.db3";
        c = DriverManager.getConnection(stringConnection);
        genericDAO = new GenericDAOImp() {
        };

    }

    public void closeConnection() throws SQLException {
        c.close();
    }

    public void openConnection() throws SQLException {
        c = DriverManager.getConnection(stringConnection);
    }

    public void fillSqliteDataBase(AccessObjectRepo accessObjectRepo) throws SQLException {
        //insertCodIne(accessObjectRepo.getCodIne());
        insertAgenda(accessObjectRepo.getAgenda());
        insertApiario(accessObjectRepo.getApiario());
        insertApicultor(accessObjectRepo.getApicultor());
        insertAssociacoes(accessObjectRepo.getAssociacoes());
        insertClassificacao(accessObjectRepo.getClassificassao());
        insertCriaRainha(accessObjectRepo.getCriaRainhas());
        insertEstrados(accessObjectRepo.getEstrados());
        insertEnXame(accessObjectRepo.getEnxame());
        insertFAlimentacao(accessObjectRepo.getFormaAlimentacao());
        insertGDefesa(accessObjectRepo.getGrauDefesa());
        insertGrupos(accessObjectRepo.getGrupos());
        insertGruposActiv(accessObjectRepo.getGrupoActividade());
        insertHistorico(accessObjectRepo.getHistorico());
        insertManutencoes(accessObjectRepo.getManutencoes());
        insertManutencoesColm(accessObjectRepo.getManutencoesColm());
        insertMedicamentos(accessObjectRepo.getMedicamentos());
        insertMelgueiras(accessObjectRepo.getMelgueiras());
        insertMetodosCriacao(accessObjectRepo.getMetosCriacao());
        insertMetodosIntro(accessObjectRepo.getMetodosIntro());
        insertNivelReserva(accessObjectRepo.getNiveisReservas());
        insertPragas(accessObjectRepo.getPragas());
        insertQualidadeMel(accessObjectRepo.getQualidadeMel());
        insertRacas(accessObjectRepo.getRacas());
        insertRainhas(accessObjectRepo.getRainhas());
        insertRegAlimentacao(accessObjectRepo.getRegAlimentacoes());
        insertRegExames(accessObjectRepo.getRegEnxames());
        insertRegPragas(accessObjectRepo.getRegPragas());
        insertTipoEPolen(accessObjectRepo.getTipoExistenciaFlores());
        insertTipoAlimentacao(accessObjectRepo.getTipoAlimentacao());
        insertTipoColmeia(accessObjectRepo.getTipoColmeia());
        insertTipoIntervencao(accessObjectRepo.getTiposIntervencao());
        insertTipoOrigem(accessObjectRepo.getTiposOrigem());
        insertTrocasCera(accessObjectRepo.getTrocasCera());

    }

    public void fillAccessObjects() throws SQLException {

        fillRegPragas();
        updateRegPragas();
        fillAgenda();
        updateAgenda();
        fillRegAlimentacoes();
        fillManutencoes();
        updateManutencoes();
        fillManutencoesColm();
        updateManutencoesColm();
        fillEnxame();
        updateEnxame();
        fillRegEnxames();
        updateRegEnxames();
        fillMelgueiras();
        updateMelgueiras();

    }

    public void insertCodIne(List<CodIne> codIne) throws SQLException {

        PreparedStatement ps = null;

        for (CodIne cod_Ine : codIne) {

            ps = c.prepareStatement(
                    "INSERT INTO cod_ine VALUES (?, ?, ?, ?)");
            ps.setString(1, cod_Ine.getCodIne());
            ps.setString(2, cod_Ine.getConcelho());
            ps.setString(3, cod_Ine.getDistrito());
            ps.setString(4, cod_Ine.getFreguesia());
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertAgenda(List<AgendaApis> agendaL) throws SQLException {

        PreparedStatement ps = null;

        for (AgendaApis agenda : agendaL) {

            ps = c.prepareStatement(
                    "INSERT INTO agenda VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?)");
            ps.setInt(1, RepoHelper.verifyInt(agenda.getCodAgenda()));
            ps.setInt(2, RepoHelper.verifyInt(agenda.getCodigoapicultor()));
            ps.setInt(3, RepoHelper.verifyShortToInt(agenda.getGrupoact()));
            ps.setInt(4, RepoHelper.verifyInt(agenda.getData()));
            ps.setInt(5, RepoHelper.verifyShortToInt(agenda.getDia()));
            ps.setInt(6, RepoHelper.verifyShortToInt(agenda.getMes()));
            ps.setString(7, RepoHelper.verifyString(agenda.getDatanormal()));
            ps.setString(8, RepoHelper.verifyString(agenda.getHora()));
            ps.setInt(9, RepoHelper.verifyInt(agenda.getTipoTarefa()));
            ps.setString(10, RepoHelper.verifyString(agenda.getTarefa()));
            ps.setInt(11, RepoHelper.verifyInt(agenda.getCdapiario()));
            ps.setString(12, RepoHelper.verifyString(agenda.getObservacoes()));
            ps.setBoolean(13, RepoHelper.verifyBoolean(agenda.getConcluido()));
            ps.setInt(14, RepoHelper.verifyShortToInt(agenda.getLembrarantes1()));
            ps.setString(15, RepoHelper.verifyDateToString(agenda.getDataalerta()));
            ps.setString(16, RepoHelper.verifyDateToString(agenda.getHoraalerta()));
            ps.setBoolean(16, RepoHelper.verifyBoolean(agenda.getAlertaautomatico()));
            ps.setInt(16, RepoHelper.verifyInt(agenda.getCodUtilizador()));
            ps.setBoolean(17, false);
            ps.setBoolean(18, false);
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertApiario(List<Apiario> ApiarioL) throws SQLException {

        PreparedStatement ps = null;

        for (Apiario apiario : ApiarioL) {

            ps = c.prepareStatement(
                    "INSERT INTO apiario VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            ps.setInt(1, RepoHelper.verifyShortToInt(apiario.getCodApiario()));
            ps.setInt(2, RepoHelper.verifyInt(apiario.getCodApicultor()));
            ps.setInt(3, RepoHelper.verifyInt(apiario.getNumApiario()));
            ps.setString(4, RepoHelper.verifyString(apiario.getIndentificacao()));
            ps.setString(5, RepoHelper.verifyDateToString(apiario.getDataInstalacao()));
            ps.setString(6, RepoHelper.verifyString(apiario.getLocalizacao()));
            ps.setString(7, RepoHelper.verifyString(apiario.getObs()));
            ps.setInt(8, RepoHelper.verifyInt(apiario.getNColonias()));
            ps.setString(9, RepoHelper.verifyString(apiario.getAltura()));
            ps.setString(10, RepoHelper.verifyString(apiario.getLargura()));
            ps.setString(11, RepoHelper.verifyString(apiario.getLatitude()));
            ps.setString(12, RepoHelper.verifyString(apiario.getLongitude()));
            ps.setString(13, RepoHelper.verifyString(apiario.getOrientacao()));
            ps.setInt(14, RepoHelper.verifyDouble(apiario.getProxiAgua()));
            ps.setString(15, RepoHelper.verifyString(apiario.getLocal()));
            ps.setBoolean(16, apiario.getZonaControlada());
            ps.setBoolean(17, apiario.getCultIntensa());
            ps.setBoolean(18, apiario.getTransumanico());
            ps.setBoolean(19, apiario.getBilogico());
            ps.setBoolean(20, apiario.getLab());
            ps.setBoolean(21, apiario.getDesactivar());
            ps.setString(22, RepoHelper.verifyString(apiario.getDistrito()));
            ps.setString(23, RepoHelper.verifyString(apiario.getConcelho()));
            ps.setString(24, RepoHelper.verifyString(apiario.getFreguesia()));
            ps.setString(25, RepoHelper.verifyString(apiario.getCodpostal()));
            ps.setString(26, RepoHelper.verifyString(apiario.getCodigoresidencia()));
            ps.setString(27, RepoHelper.verifyString(apiario.getFoto()));
            ps.setString(28, RepoHelper.verifyString(apiario.getZonacontrol()));
            ps.setInt(29, RepoHelper.verifyDouble(apiario.getProdMel()));
            ps.setInt(30, RepoHelper.verifyDouble(apiario.getProdPropolis()));
            ps.setInt(31, RepoHelper.verifyDouble(apiario.getProdPolen()));
            ps.setDate(32, null);
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertApicultor(List<Apicultor> apicultorL) throws SQLException {

        PreparedStatement ps = null;

        for (Apicultor apicultor : apicultorL) {

            ps = c.prepareStatement(
                    "INSERT INTO apicultor VALUES (?, ?, ?, ?,?, ?, ?, ?,?, ?, ?, ?,?, ?, ?, ?,?,?)");
            ps.setInt(1, RepoHelper.verifyInt(apicultor.getNApicultor()));
            ps.setString(2, RepoHelper.verifyString(apicultor.getNome()));
            ps.setString(3, RepoHelper.verifyString(apicultor.getMorada()));
            ps.setString(4, RepoHelper.verifyString(apicultor.getCodPostal()));
            ps.setInt(5, RepoHelper.verifyInt(apicultor.getNif()));
            ps.setString(6, RepoHelper.verifyString(apicultor.getNacNif()));
            ps.setInt(7, RepoHelper.verifyInt(apicultor.getBi()));
            if (apicultor.getAssociacoes() != null) {
                ps.setInt(8, RepoHelper.verifyInt(apicultor.getAssociacoes().getCodAsso()));
            }
            ps.setInt(9, RepoHelper.verifyInt(apicultor.getNSocio()));
            ps.setString(10, RepoHelper.verifyString(apicultor.getTelefone()));
            ps.setString(11, RepoHelper.verifyString(apicultor.getTelemovel()));
            ps.setString(12, RepoHelper.verifyString(apicultor.getFax()));
            ps.setString(13, RepoHelper.verifyString(apicultor.getEmail()));
            ps.setString(14, RepoHelper.verifyDateToString(apicultor.getDataCriacao()));
            ps.setString(15, RepoHelper.verifyString(apicultor.getObs()));
            ps.setBoolean(16, apicultor.getPropri());
            ps.setString(17, RepoHelper.verifyString(apicultor.getCodresi()));
            ps.setString(18, RepoHelper.verifyString(apicultor.getFoto()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertAssociacoes(List<Associacoes> associacoesL) throws SQLException {

        PreparedStatement ps = null;

        for (Associacoes associacao : associacoesL) {
            ps = c.prepareStatement(
                    "INSERT INTO associacoes VALUES (?, ?)");
            ps.setInt(1, RepoHelper.verifyInt(associacao.getCodAsso()));
            ps.setString(2, RepoHelper.verifyString(associacao.getDescricao()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertClassificacao(List<Classificasao> classificacaoL) throws SQLException {

        PreparedStatement ps = null;

        for (Classificasao classificacao : classificacaoL) {
            ps = c.prepareStatement(
                    "INSERT INTO classificasao VALUES (?, ?)");
            ps.setInt(1, RepoHelper.verifyInt(classificacao.getCodClass()));
            ps.setString(2, RepoHelper.verifyString(classificacao.getDescricao()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertCriaRainha(List<CriaRainhas> criaRainhaL) throws SQLException {

        PreparedStatement ps = null;

        for (CriaRainhas criaRainha : criaRainhaL) {
            ps = c.prepareStatement(
                    "INSERT INTO cria_rainhas VALUES (?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?)");
            ps.setInt(1, RepoHelper.verifyInt(criaRainha.getId().getIdmest()));
            ps.setString(2, RepoHelper.verifyString(criaRainha.getId().getCodRainha()));
            ps.setInt(3, RepoHelper.verifyInt(criaRainha.getApiarioDador()));
            ps.setString(4, RepoHelper.verifyString(criaRainha.getColmeiaDadora()));
            ps.setInt(5, RepoHelper.verifyInt(criaRainha.getApiarioInicializ()));
            ps.setString(6, RepoHelper.verifyString(criaRainha.getColmeiaInicializ()));
            ps.setInt(7, RepoHelper.verifyInt(criaRainha.getApiarioFinaliz()));
            ps.setString(8, RepoHelper.verifyString(criaRainha.getColmeiaFinaliz()));
            ps.setInt(9, RepoHelper.verifyInt(criaRainha.getApiarioFecunda()));
            ps.setString(10, RepoHelper.verifyString(criaRainha.getColmeiaFecunda()));
            if (criaRainha.getMetodosCriacao() != null) {
                ps.setInt(11, RepoHelper.verifyInt(criaRainha.getMetodosCriacao().getIdMetCria()));
            }
            ps.setString(12, RepoHelper.verifyDateToString(criaRainha.getDataFecundacao()));
            ps.setBoolean(13, criaRainha.getCompra());
            ps.setString(14, RepoHelper.verifyString(criaRainha.getRacaRainha()));
            ps.setString(15, RepoHelper.verifyString(criaRainha.getAvaliacao()));
            ps.setString(16, RepoHelper.verifyDateToString(criaRainha.getDataTroca()));
            ps.setBoolean(17, criaRainha.getMarcaClipada());
            ps.setString(18, RepoHelper.verifyString(criaRainha.getAno()));
            ps.setString(19, RepoHelper.verifyString(criaRainha.getCor()));
            ps.setBoolean(20, criaRainha.getActiva());
            ps.setString(21, RepoHelper.verifyString(criaRainha.getMotivo()));
            ps.setString(22, RepoHelper.verifyString(criaRainha.getObs()));
            ps.setInt(23, RepoHelper.verifyInt(criaRainha.getApFinal()));
            ps.setString(24, RepoHelper.verifyString(criaRainha.getColmeiaFinal()));
            ps.setInt(25, RepoHelper.verifyInt(criaRainha.getCodApicultor()));
            ps.setInt(26, RepoHelper.verifyInt(criaRainha.getCodUtilizador()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertEstrados(List<Estrados> estradosL) throws SQLException {

        PreparedStatement ps = null;

        for (Estrados estrados : estradosL) {
            ps = c.prepareStatement(
                    "INSERT INTO estrados VALUES (?, ?)");
            ps.setInt(1, RepoHelper.verifyInt(estrados.getId()));
            ps.setString(2, RepoHelper.verifyString(estrados.getNome()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertEnXame(List<Enxame> enxameL) throws SQLException {

        PreparedStatement ps = null;

        for (Enxame enxame : enxameL) {
            ps = c.prepareStatement(
                    "INSERT INTO exame VALUES (?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?,?)");
            ps.setInt(1, RepoHelper.verifyInt(enxame.getCodExame()));
            ps.setInt(2, RepoHelper.verifyInt(enxame.getCodApiario()));
            ps.setString(3, RepoHelper.verifyString(enxame.getNumeroColonia()));
            ps.setString(4, RepoHelper.verifyString(enxame.getCor()));
            if (enxame.getTiposOrigem() != null) {
                ps.setInt(5, RepoHelper.verifyInt(enxame.getTiposOrigem().getCodTipo()));
            }
            ps.setInt(6, RepoHelper.verifyInt(enxame.getCodApiarioOrigem()));
            ps.setString(7, RepoHelper.verifyString(enxame.getColoniaOrigem()));
            ps.setString(8, RepoHelper.verifyString(enxame.getDataFormacao()));
            ps.setInt(9, RepoHelper.verifyInt(enxame.getTipoNucleo()));
            ps.setString(10, RepoHelper.verifyString(enxame.getRaca()));
            ps.setString(11, RepoHelper.verifyString(enxame.getDataInstalacao()));
            ps.setString(12, RepoHelper.verifyString(enxame.getQualidade()));
            ps.setBoolean(13, enxame.getProbEnxemaecao());
            ps.setBoolean(14, enxame.getPossuiredutoralvado());
            ps.setBoolean(15, enxame.getPossuitampoexcluidor());
            ps.setBoolean(16, enxame.getCaptpolen());
            ps.setInt(17, RepoHelper.verifyInt(enxame.getQuantidadealsa()));
            ps.setInt(18, RepoHelper.verifyInt(enxame.getNAlimentacoes()));
            ps.setInt(19, RepoHelper.verifyInt(enxame.getNivelReservas()));
            ps.setInt(20, RepoHelper.verifyInt(enxame.getQtdAlimenta()));
            ps.setString(21, RepoHelper.verifyString(enxame.getMotivoDesact()));
            ps.setString(22, RepoHelper.verifyString(enxame.getNivelDefesa()));
            ps.setString(23, RepoHelper.verifyString(enxame.getObs()));
            if (enxame.getEstrados() != null) {
                ps.setInt(24, RepoHelper.verifyInt(enxame.getEstrados().getId()));
            }
            ps.setInt(25, RepoHelper.verifyInt(enxame.getPosX()));
            ps.setInt(26, RepoHelper.verifyInt(enxame.getPosY()));
            ps.setString(27, RepoHelper.verifyString(enxame.getCodabar()));
            ps.setString(28, RepoHelper.verifyString(enxame.getEtiqrdif()));
            if (!(enxame.getTempInterior() == null)) {
                ps.setInt(29, RepoHelper.verifyInt(enxame.getTempInterior().intValueExact()));
            } else {
                ps.setInt(29, 0);
            }
            if (!(enxame.getHumidInterior() == null)) {
                ps.setInt(30, RepoHelper.verifyInt(enxame.getHumidInterior().intValueExact()));
            } else {
                ps.setInt(30, 0);
            }

            if (!(enxame.getNivRuido() == null)) {
                ps.setInt(31, RepoHelper.verifyInt(enxame.getNivRuido().intValueExact()));
            } else {
                ps.setInt(31, 0);
            }

            ps.setString(32, RepoHelper.verifyString(enxame.getLocalizaLatid()));
            ps.setString(33, RepoHelper.verifyString(enxame.getLocalizaLongitude()));
            ps.setBoolean(34, enxame.getDesactiva());
            ps.setInt(35, RepoHelper.verifyInt(enxame.getGrupo()));
            ps.setString(36, RepoHelper.verifyString(enxame.getFoto()));
            ps.setBoolean(37, enxame.getTabdetectvaroa());
            ps.setInt(38, RepoHelper.verifyDouble(enxame.getProdMel()));
            ps.setInt(39, RepoHelper.verifyDouble(enxame.getProdPolen()));
            ps.setInt(40, RepoHelper.verifyDouble(enxame.getProdPropolis()));
            ps.setBoolean(41, false);
            ps.setBoolean(42, false);
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertFAlimentacao(List<FormaAlimentacao> fAlimentacaoL) throws SQLException {

        PreparedStatement ps = null;

        for (FormaAlimentacao fAlimentacao : fAlimentacaoL) {
            ps = c.prepareStatement(
                    "INSERT INTO forma_alimentacao VALUES (?, ?)");
            ps.setInt(1, RepoHelper.verifyInt(fAlimentacao.getId()));
            ps.setString(2, RepoHelper.verifyString(fAlimentacao.getDescricao()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertGDefesa(List<GrauDefesa> gDefesaL) throws SQLException {

        PreparedStatement ps = null;

        for (GrauDefesa gDefesa : gDefesaL) {
            ps = c.prepareStatement(
                    "INSERT INTO grau_defesa VALUES (?, ?)");
            ps.setInt(1, RepoHelper.verifyInt(gDefesa.getId()));
            ps.setString(2, RepoHelper.verifyString(gDefesa.getDescricao()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertGrupos(List<Grupos> gruposL) throws SQLException {

        PreparedStatement ps = null;

        for (Grupos grupos : gruposL) {
            ps = c.prepareStatement(
                    "INSERT INTO grupos VALUES (?, ?)");
            ps.setInt(1, RepoHelper.verifyInt(grupos.getIdgrupo()));
            ps.setString(2, RepoHelper.verifyString(grupos.getDescricao()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertGruposActiv(List<Gruposactiv> gruposActivL) throws SQLException {

        PreparedStatement ps = null;

        for (Gruposactiv gruposActiv : gruposActivL) {
            ps = c.prepareStatement(
                    "INSERT INTO gruposactiv VALUES (?, ?)");
            ps.setInt(1, RepoHelper.verifyInt(gruposActiv.getIdgrupo()));
            ps.setString(2, RepoHelper.verifyString(gruposActiv.getDescricao()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertHistorico(List<Historico> historicoL) throws SQLException {

        PreparedStatement ps = null;

        for (Historico historico : historicoL) {
            ps = c.prepareStatement(
                    "INSERT INTO historico VALUES (?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?)");
            ps.setInt(1, RepoHelper.verifyInt(historico.getIdhist()));
            ps.setString(2, RepoHelper.verifyDateToString(historico.getData()));
            ps.setInt(3, RepoHelper.verifyInt(historico.getCodColmeia()));
            ps.setString(4, RepoHelper.verifyString(historico.getColmeia()));
            ps.setString(5, RepoHelper.verifyString(historico.getTipocaixa()));
            ps.setString(6, RepoHelper.verifyString(historico.getGrupo()));
            ps.setString(7, RepoHelper.verifyString(historico.getTipoextrado()));
            ps.setString(8, RepoHelper.verifyString(historico.getNiveldefesa()));
            ps.setString(9, RepoHelper.verifyString(historico.getProdutividade()));
            ps.setString(10, RepoHelper.verifyString(historico.getNivelreserva()));
            ps.setInt(11, RepoHelper.verifyInt(historico.getQtdAlimenta()));
            ps.setBoolean(12, historico.getPossuiredutoralvado());
            ps.setBoolean(13, historico.getPossiutampoecluidor());
            ps.setBoolean(14, historico.getPossiutabdetecvaroa());
            ps.setBoolean(15, historico.getPossiucaptpolen());
            ps.setInt(16, RepoHelper.verifyInt(historico.getQtdAlsa()));
            ps.setString(17, RepoHelper.verifyString(historico.getNiveilreserva()));
            ps.setBoolean(18, historico.getProbenxemacao());
            ps.setString(19, String.valueOf(RepoHelper.verifyDouble(historico.getProdMel())));
            ps.setString(20, String.valueOf(RepoHelper.verifyDouble(historico.getProdPolen())));
            ps.setString(21, String.valueOf(RepoHelper.verifyDouble(historico.getProdPropolis())));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertManutencoes(List<Manutencoes> manutencoesL) throws SQLException {

        PreparedStatement ps = null;

        for (Manutencoes manutencoes : manutencoesL) {
            ps = c.prepareStatement(
                    "INSERT INTO manutencoes VALUES (?, ?,?, ?,?, ?,?, ?,?,?,?)");

            ps.setInt(1, RepoHelper.verifyInt(manutencoes.getCodManu()));
            ps.setString(2, RepoHelper.verifyDateToString(manutencoes.getData()));
            ps.setInt(3, RepoHelper.verifyInt(manutencoes.getCodApiario()));
            ps.setString(4, RepoHelper.verifyString(manutencoes.getNomeApiario()));
            if (manutencoes.getTiposIntervencao() != null) {
                ps.setInt(5, RepoHelper.verifyInt(manutencoes.getTiposIntervencao().getIdTipIntr()));
            }
            ps.setString(6, RepoHelper.verifyString(manutencoes.getDescricao()));
            ps.setString(7, RepoHelper.verifyString(manutencoes.getAno()));
            ps.setInt(8, RepoHelper.verifyInt(manutencoes.getCodApicultor()));
            ps.setInt(9, RepoHelper.verifyInt(manutencoes.getCodUtilizador()));
            ps.setBoolean(10, false);
            ps.setBoolean(11, false);
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertManutencoesColm(List<Manutencoescolm> manutencoesColmL) throws SQLException {

        PreparedStatement ps = null;

        for (Manutencoescolm manutencoescolm : manutencoesColmL) {
            ps = c.prepareStatement(
                    "INSERT INTO manutencoescolm VALUES (?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?,?,?)");
            ps.setInt(1, RepoHelper.verifyInt(manutencoescolm.getCodmanu()));
            ps.setString(2, RepoHelper.verifyDateToString(manutencoescolm.getData()));
            ps.setString(3, RepoHelper.verifyString(manutencoescolm.getAno()));
            ps.setInt(4, RepoHelper.verifyInt(manutencoescolm.getCodap()));
            ps.setInt(5, RepoHelper.verifyInt(manutencoescolm.getCodcolm()));
            ps.setString(6, RepoHelper.verifyString(manutencoescolm.getNumcolm()));
            ps.setInt(7, RepoHelper.verifyInt(manutencoescolm.getTipointrev()));
            ps.setString(8, RepoHelper.verifyString(manutencoescolm.getDescricao()));
            ps.setString(9, RepoHelper.verifyString(manutencoescolm.getTemperatura()));
            ps.setString(10, RepoHelper.verifyString(manutencoescolm.getHumidade()));
            ps.setInt(11, RepoHelper.verifyInt(manutencoescolm.getDoencadetct()));
            ps.setInt(12, RepoHelper.verifyInt(manutencoescolm.getTrafego()));
            ps.setInt(13, RepoHelper.verifyInt(manutencoescolm.getPadraopostura()));
            ps.setInt(14, RepoHelper.verifyInt(manutencoescolm.getNCupReais()));
            ps.setInt(15, RepoHelper.verifyInt(manutencoescolm.getNOvosdia()));
            ps.setInt(16, RepoHelper.verifyInt(manutencoescolm.getEntrapolen()));
            ps.setInt(17, RepoHelper.verifyInt(manutencoescolm.getNivReserv()));
            ps.setBoolean(18, false);
            ps.setBoolean(19, false);
            ps.executeUpdate();
            ps.close();

        }
    }

    public void insertMedicamentos(List<Medicamentos> medicamentosL) throws SQLException {

        PreparedStatement ps = null;

        for (Medicamentos medicamentos : medicamentosL) {
            ps = c.prepareStatement(
                    "INSERT INTO Medicamentos VALUES (?, ?,?, ?,?, ?,?, ?)");

            ps.setInt(1, RepoHelper.verifyInt(medicamentos.getIdMedicamento()));
            ps.setString(2, RepoHelper.verifyString(medicamentos.getNome()));
            ps.setString(3, RepoHelper.verifyString(medicamentos.getIndicacoes()));
            ps.setString(4, RepoHelper.verifyString(medicamentos.getPerparacao()));
            ps.setString(5, RepoHelper.verifyString(medicamentos.getEfeitosSec()));
            ps.setBoolean(6, medicamentos.getBiologico());
            ps.setString(7, RepoHelper.verifyString(medicamentos.getLaboratorio()));
            ps.setInt(8, RepoHelper.verifyInt(medicamentos.getDiasAplica()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertMelgueiras(List<Melgueiras> melgueirasL) throws SQLException {

        PreparedStatement ps = null;

        for (Melgueiras melgueiras : melgueirasL) {
            ps = c.prepareStatement(
                    "INSERT INTO melgueiras VALUES (?, ?,?, ?,?, ?,?, ?,?, ?,?, ?,?,?,?,?)");

            ps.setInt(1, Integer.valueOf(RepoHelper.verifyString(melgueiras.getCodMelgueira())));
            ps.setInt(2, RepoHelper.verifyInt(melgueiras.getCodApiario()));
            if (melgueiras.getEnxame() != null) {
                ps.setInt(3, RepoHelper.verifyInt(melgueiras.getEnxame().getCodExame()));
            } else {
                ps.setInt(3, 0);
            }
            ps.setString(4, RepoHelper.verifyString(melgueiras.getDesignacao()));
            ps.setString(5, RepoHelper.verifyDateToString(melgueiras.getDataColocacao()));
            ps.setString(6, RepoHelper.verifyDateToString(melgueiras.getDataExtracacao()));
            ps.setString(7, RepoHelper.verifyString(melgueiras.getTipo()));
            ps.setInt(8, Integer.valueOf(String.valueOf(RepoHelper.verifyDouble(melgueiras.getPeso()))));
            ps.setInt(9, Integer.valueOf(String.valueOf(RepoHelper.verifyDouble(melgueiras.getTara()))));
            ps.setBoolean(10, melgueiras.getExtraida());
            ps.setString(11, RepoHelper.verifyString(melgueiras.getObservacoes()));
            ps.setInt(12, RepoHelper.verifyInt(melgueiras.getCodApicultor()));
            ps.setInt(13, RepoHelper.verifyInt(melgueiras.getCodUtilizador()));
            ps.setInt(14, RepoHelper.verifyInt(melgueiras.getTipoalca()));
            ps.setBoolean(15, false);
            ps.setBoolean(16, false);
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertMetodosCriacao(List<MetodosCriacao> metodosCriacaoL) throws SQLException {

        PreparedStatement ps = null;

        for (MetodosCriacao metodosCriacao : metodosCriacaoL) {
            ps = c.prepareStatement(
                    "INSERT INTO metodos_criacao VALUES (?, ?,?)");

            ps.setInt(1, RepoHelper.verifyInt(metodosCriacao.getIdMetCria()));
            ps.setString(2, RepoHelper.verifyString(metodosCriacao.getMetodo()));
            ps.setString(3, RepoHelper.verifyString(metodosCriacao.getDescricao()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertMetodosIntro(List<MetodosIntro> MetodosIntroL) throws SQLException {

        PreparedStatement ps = null;

        for (MetodosIntro metodosIntro : MetodosIntroL) {
            ps = c.prepareStatement(
                    "INSERT INTO metodos_intro VALUES (?, ?,?)");

            ps.setInt(1, RepoHelper.verifyInt(metodosIntro.getIdMetodointro()));
            ps.setString(2, RepoHelper.verifyString(metodosIntro.getMetodo()));
            ps.setString(3, RepoHelper.verifyString(metodosIntro.getDescricao()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertNivelReserva(List<NiveisReservas> niveisReservasL) throws SQLException {

        PreparedStatement ps = null;

        for (NiveisReservas niveisReservas : niveisReservasL) {
            ps = c.prepareStatement(
                    "INSERT INTO niveis_reservas VALUES (?, ?)");

            ps.setInt(1, RepoHelper.verifyInt(niveisReservas.getIdnreserva()));
            ps.setString(2, RepoHelper.verifyString(niveisReservas.getDescricao()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertPragas(List<Pragas> pragasL) throws SQLException {

        PreparedStatement ps = null;

        for (Pragas pragas : pragasL) {
            ps = c.prepareStatement(
                    "INSERT INTO pragas VALUES (?, ?,?, ?,?, ?,?, ?,?)");

            ps.setInt(1, RepoHelper.verifyInt(pragas.getCodPraga()));
            ps.setString(2, RepoHelper.verifyString(pragas.getDoenca()));
            ps.setString(3, RepoHelper.verifyString(pragas.getAgente()));
            ps.setString(4, RepoHelper.verifyString(pragas.getSintomas()));
            ps.setString(5, RepoHelper.verifyString(pragas.getMeioContagio()));
            ps.setString(6, RepoHelper.verifyString(pragas.getTratamento()));
            ps.setString(7, RepoHelper.verifyString(pragas.getOcorrencia()));
            ps.setString(8, RepoHelper.verifyString(pragas.getDano()));
            ps.setString(9, RepoHelper.verifyString(pragas.getFoto()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertQualidadeMel(List<QualidMel> qualidMelL) throws SQLException {

        PreparedStatement ps = null;

        for (QualidMel qualidMel : qualidMelL) {
            ps = c.prepareStatement(
                    "INSERT INTO Qualid_mel VALUES (?, ?)");
            ps.setInt(1, RepoHelper.verifyInt(qualidMel.getIdQualid()));
            ps.setString(2, RepoHelper.verifyString(qualidMel.getDescicao()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertRacas(List<Racas> racasL) throws SQLException {

        PreparedStatement ps = null;

        for (Racas racas : racasL) {
            ps = c.prepareStatement(
                    "INSERT INTO racas VALUES (?, ?)");
            ps.setString(1, RepoHelper.verifyString(racas.getCodRaca()));
            ps.setString(2, RepoHelper.verifyString(racas.getDescricao()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertRainhas(List<Rainhas> rainhasL) throws SQLException {

        PreparedStatement ps = null;

        for (Rainhas rainhas : rainhasL) {
            ps = c.prepareStatement(
                    "INSERT INTO rainhas VALUES (?, ?,?,?,?,?, ?,?, ?,?,?,?,?)");
            ps.setInt(1, RepoHelper.verifyInt(rainhas.getCodTrocRainhas()));
            ps.setInt(2, RepoHelper.verifyInt(rainhas.getCodApiario()));
            if (rainhas.getEnxame() != null) {
                ps.setInt(3, RepoHelper.verifyInt(rainhas.getEnxame().getCodExame()));
            }
            ps.setString(4, RepoHelper.verifyString(rainhas.getNumeroColmeia()));
            ps.setInt(5, RepoHelper.verifyInt(rainhas.getApiaOrigem()));
            ps.setInt(6, Integer.valueOf(RepoHelper.verifyString(rainhas.getColOrigem())));
            ps.setString(7, RepoHelper.verifyString(rainhas.getCodRainha()));
            if (rainhas.getClassificasao() != null) {
                ps.setInt(8, RepoHelper.verifyInt(rainhas.getClassificasao().getCodClass()));
            }
            ps.setString(9, RepoHelper.verifyString(rainhas.getDataintroducao()));
            if (rainhas.getMetodosIntro() != null) {
                ps.setInt(10, RepoHelper.verifyInt(rainhas.getMetodosIntro().getIdMetodointro()));
            }
            ps.setString(11, RepoHelper.verifyString(rainhas.getObs()));
            ps.setInt(12, RepoHelper.verifyInt(rainhas.getCodApicultor()));
            ps.setInt(13, RepoHelper.verifyInt(rainhas.getCodUtilizador()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertRegAlimentacao(List<RegAlimentacoes> regAlimentacoesL) throws SQLException {

        PreparedStatement ps = null;

        for (RegAlimentacoes regAlimentacoes : regAlimentacoesL) {
            ps = c.prepareStatement(
                    "INSERT INTO reg_alimentacoes VALUES (?, ?,?, ?,?,?,?,?, ?,?, ?,?,?,?,?,?)");

            ps.setInt(1, RepoHelper.verifyInt(regAlimentacoes.getCodAlimentacao()));
            if (regAlimentacoes.getEnxame() != null) {
                ps.setInt(2, RepoHelper.verifyInt(regAlimentacoes.getEnxame().getCodExame()));
            }
            ps.setString(3, RepoHelper.verifyString(regAlimentacoes.getNomeEnxame()));
            ps.setString(4, RepoHelper.verifyString(regAlimentacoes.getAno()));
            ps.setString(5, RepoHelper.verifyString(regAlimentacoes.getMes()));
            ps.setString(6, RepoHelper.verifyDateToString(regAlimentacoes.getData()));
            if (regAlimentacoes.getTipoAlimentacao() != null) {
                ps.setInt(7, RepoHelper.verifyInt(regAlimentacoes.getTipoAlimentacao().getCodTipAlim()));
            }
            if (regAlimentacoes.getFormaAlimentacao() != null) {
                ps.setInt(8, RepoHelper.verifyInt(regAlimentacoes.getFormaAlimentacao().getId()));
            }
            ps.setInt(9, RepoHelper.verifyDouble(regAlimentacoes.getQuantidade()));
            ps.setDouble(10, RepoHelper.verifyDouble(regAlimentacoes.getCustoEstimado()));
            ps.setString(11, RepoHelper.verifyString(regAlimentacoes.getObjectivo()));
            ps.setInt(12, RepoHelper.verifyInt(regAlimentacoes.getCodApairio()));
            ps.setInt(13, RepoHelper.verifyInt(regAlimentacoes.getCodApicultor()));
            ps.setInt(14, RepoHelper.verifyInt(regAlimentacoes.getCodUtilizador()));
            ps.setInt(15, RepoHelper.verifyInt(regAlimentacoes.getDiasproxali()));
            ps.setBoolean(16, false);
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertRegExames(List<RegEnxames> regEnxamesL) throws SQLException {

        PreparedStatement ps = null;

        for (RegEnxames regEnxames : regEnxamesL) {
            ps = c.prepareStatement(
                    "INSERT INTO reg_enxames VALUES (?, ?,?,?,?,?, ?,?, ?,?,?,?,?,?)");

            ps.setInt(1, RepoHelper.verifyInt(regEnxames.getExamId()));
            ps.setInt(2, RepoHelper.verifyInt(regEnxames.getApiario()));
            ps.setInt(3, RepoHelper.verifyInt(regEnxames.getColmOrigem()));
            ps.setString(4, RepoHelper.verifyString(regEnxames.getAno()));
            ps.setString(5, RepoHelper.verifyDateToString(regEnxames.getDataEnxemeacao()));
            ps.setString(6, RepoHelper.verifyString(regEnxames.getDiscricao()));
            ps.setString(7, RepoHelper.verifyString(regEnxames.getColmOriginada()));
            ps.setInt(8, RepoHelper.verifyInt(regEnxames.getCodApicultor()));
            ps.setInt(9, RepoHelper.verifyInt(regEnxames.getCodUtilizador()));
            ps.setInt(10, RepoHelper.verifyShortToInt(regEnxames.getTipoenxame()));
            ps.setInt(11, RepoHelper.verifyShortToInt(regEnxames.getTipodesdobra()));
            ps.setInt(12, RepoHelper.verifyShortToInt(regEnxames.getSucesso()));
            ps.setBoolean(13, false);
            ps.setBoolean(14, false);
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertRegPragas(List<RegPragas> regPragasL) throws SQLException {

        PreparedStatement ps = null;

        for (RegPragas regPragas : regPragasL) {
            ps = c.prepareStatement(
                    "INSERT INTO reg_pragas VALUES (?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?,?,?,?)");

            ps.setInt(1, RepoHelper.verifyInt(regPragas.getCodReg()));
            ps.setInt(2, RepoHelper.verifyInt(regPragas.getQuarentena()));
            ps.setInt(3, RepoHelper.verifyInt(regPragas.getCodApiario()));
            if (regPragas.getEnxame() != null) {
                ps.setInt(4, RepoHelper.verifyInt(regPragas.getEnxame().getCodExame()));
            }
            ps.setString(5, RepoHelper.verifyString(regPragas.getNomeEnxame()));
            Integer i = null;
            ps.setInt(6, RepoHelper.verifyInt(regPragas.getCodUtilizador()));
            ps.setInt(7, RepoHelper.verifyInt(regPragas.getCodApicultor()));
            if (regPragas.getPragas() != null) {
                ps.setInt(8, RepoHelper.verifyInt(regPragas.getPragas().getCodPraga()));
            }
            ps.setString(9, RepoHelper.verifyString(regPragas.getData()));
            ps.setString(10, RepoHelper.verifyString(regPragas.getDescricao()));
            ps.setString(11, RepoHelper.verifyString(regPragas.getVeternario()));
            ps.setBoolean(12, regPragas.getTratamento());
            ps.setBoolean(13, regPragas.getAbate());
            ps.setInt(14, RepoHelper.verifyInt(regPragas.getMedicamento()));
            ps.setString(15, RepoHelper.verifyString(regPragas.getDataretirar()));
            ps.setBoolean(16, regPragas.getRetirado());
            ps.setString(17, RepoHelper.verifyDateToString(regPragas.getDataretirado()));
            ps.setBoolean(18, false);
            ps.setBoolean(19, false);
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertTipoAlimentacao(List<TipoAlimentacao> tipoAlimentacaoL) throws SQLException {

        PreparedStatement ps = null;

        for (TipoAlimentacao tipoAlimentacao : tipoAlimentacaoL) {
            ps = c.prepareStatement(
                    "INSERT INTO tipo_alimentacao VALUES (?, ?,?,?,?)");

            ps.setInt(1, RepoHelper.verifyInt(tipoAlimentacao.getCodTipAlim()));
            ps.setString(2, RepoHelper.verifyString(tipoAlimentacao.getDescricao()));
            ps.setString(3, RepoHelper.verifyString(tipoAlimentacao.getIngredientes()));
            ps.setString(4, RepoHelper.verifyString(tipoAlimentacao.getPerparacao()));
            ps.setInt(5, RepoHelper.verifyInt(tipoAlimentacao.getQuantidade()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertTipoColmeia(List<TipoColmeia> tipoColmeiaL) throws SQLException {

        PreparedStatement ps = null;

        for (TipoColmeia tipoColmeia : tipoColmeiaL) {
            ps = c.prepareStatement(
                    "INSERT INTO tipo_colmeia VALUES (?, ?,?, ?)");

            ps.setInt(1, RepoHelper.verifyInt(tipoColmeia.getCodTipo()));
            ps.setString(2, RepoHelper.verifyString(tipoColmeia.getModelo()));
            ps.setInt(3, RepoHelper.verifyInt(tipoColmeia.getNQuadros()));
            ps.setInt(4, RepoHelper.verifyInt(tipoColmeia.getNQuadAlca()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertTipoEPolen(List<TiposExitenciaFlores> tiposIntervencaoL) throws SQLException {

        PreparedStatement ps = null;

        for (TiposExitenciaFlores tiposIntervencao : tiposIntervencaoL) {
            ps = c.prepareStatement(
                    "INSERT INTO Tipos_exitencia_flores VALUES (?, ?)");

            ps.setInt(1, RepoHelper.verifyInt(tiposIntervencao.getIdExist()));
            ps.setString(2, RepoHelper.verifyString(tiposIntervencao.getDescricao()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertTipoIntervencao(List<TiposIntervencao> tiposIntervencaoL) throws SQLException {

        PreparedStatement ps = null;

        for (TiposIntervencao tiposIntervencao : tiposIntervencaoL) {
            ps = c.prepareStatement(
                    "INSERT INTO tipos_intervencao VALUES (?, ?,?, ?,?)");

            ps.setInt(1, RepoHelper.verifyInt(tiposIntervencao.getIdTipIntr()));
            ps.setString(2, RepoHelper.verifyString(tiposIntervencao.getDescricao()));
            ps.setBoolean(3, tiposIntervencao.getSistema());
            ps.setString(4, RepoHelper.verifyString(tiposIntervencao.getScap()));
            ps.setString(5, RepoHelper.verifyString(tiposIntervencao.getTipo()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertTipoOrigem(List<TiposOrigem> tiposOrigemL) throws SQLException {

        PreparedStatement ps = null;

        for (TiposOrigem tiposOrigem : tiposOrigemL) {
            ps = c.prepareStatement(
                    "INSERT INTO tipos_origem VALUES (?, ?)");

            ps.setInt(1, RepoHelper.verifyInt(tiposOrigem.getCodTipo()));
            ps.setString(2, RepoHelper.verifyString(tiposOrigem.getDescricao()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void insertTrocasCera(List<TrocasCera> trocasCeraL) throws SQLException {

        PreparedStatement ps = null;

        for (TrocasCera trocasCera : trocasCeraL) {
            ps = c.prepareStatement(
                    "INSERT INTO trocas_cera VALUES (?, ?,?, ?,?,?, ?,?, ?,?,?)");

            ps.setInt(1, RepoHelper.verifyInt(trocasCera.getCodTroca()));
            if (trocasCera.getEnxame() != null) {
                ps.setInt(2, RepoHelper.verifyInt(trocasCera.getEnxame().getCodExame()));
            }
            ps.setString(3, RepoHelper.verifyString(trocasCera.getNomeEnxame()));
            ps.setString(4, RepoHelper.verifyString(trocasCera.getNQuadro()));
            ps.setBoolean(5, trocasCera.getTrocado());
            ps.setString(6, RepoHelper.verifyDateToString(trocasCera.getData()));
            ps.setString(7, RepoHelper.verifyString(trocasCera.getAno()));
            ps.setInt(8, RepoHelper.verifyInt(trocasCera.getCodApicultor()));
            ps.setInt(9, RepoHelper.verifyInt(trocasCera.getCodUtilizador()));
            ps.setInt(10, RepoHelper.verifyInt(trocasCera.getMotivo()));
            ps.setString(11, RepoHelper.verifyString(trocasCera.getObs()));
            ps.executeUpdate();
            ps.close();
        }
    }

    public void fillRegPragas() throws SQLException {

        try {
            Table table = dbAccess.getTable("reg_pragas");
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM reg_pragas where isNote = 1 and updateInsert = 0 ;");
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                Cursor cursor = CursorBuilder.createCursor(table);
                java.util.Date dataRetirado = null;
                java.util.Date dataRetirar = null;
                if (rs.getString("dataretirar") != null) {
                    if (!(rs.getString("dataretirar").equals(""))) {
                        SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd");
                        dataRetirar = (java.util.Date) dt.parse(rs.getString("dataretirar"));

                    }
                }
                if (rs.getString("dataretirado") != null && !(rs.getString("dataretirado").isEmpty())) {
                    SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd");
                    dataRetirado = (java.util.Date) dt.parse(rs.getString("dataretirado"));

                }
                map.put("quarentena", rs.getInt("quarentena"));
                map.put("cod_apiario", rs.getInt("cod_Apiario"));
                map.put("cod_enxame", rs.getInt("cod_Enxame"));
                map.put("nome_enxame", rs.getString("nome_enxame"));
                map.put("cod_utilizador", rs.getInt("cod_utilizador"));
                map.put("cod_apicultor", rs.getInt("cod_Apicultor"));
                map.put("cod_praga", rs.getInt("cod_Praga"));
                map.put("data", rs.getString("data"));
                map.put("descricao", rs.getString("descricao"));
                map.put("veternario", rs.getString("veternario"));
                map.put("tratamento", rs.getBoolean("tratamento"));
                map.put("Abate", rs.getBoolean("Abate"));
                map.put("medicamento", rs.getInt("medicamento"));
                map.put("dataretirar", dataRetirar);
                map.put("retirado", rs.getBoolean("retirado"));
                map.put("dataretirado", dataRetirado);
                table.addRowFromMap(map);
                Row r = null;
            }
            rs.close();
            stmt.close();
        } catch (IOException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ParseException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void updateRegPragas() throws SQLException {
        Map<String, Object> map = new HashMap<>();
        try {
            Table table = dbAccess.getTable("reg_pragas");
            Cursor cursor = CursorBuilder.createCursor(table);
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM reg_pragas where isNote = 1 and updateInsert = 1;");
            while (rs.next()) {
                Boolean b = null;
                b = rs.getBoolean("retirado");
                map.put("retirado", b);
                for (Row row : cursor.newIterable().addMatchPattern("cod_reg", rs.getInt("_idRegP"))) {
                    cursor.updateCurrentRow(table.asUpdateRow(map));
                }
                map.clear();
            }
            rs.close();
            stmt.close();
        } catch (IOException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);

        }
    }
    public void updateAgenda() throws SQLException {
        Map<String, Object> map = new HashMap<>();
        try {
            Table table = dbAccess.getTable("agenda_apis");
            Cursor cursor = CursorBuilder.createCursor(table);
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM agenda where updateInsert = 1;");
            while (rs.next()) {
                map.put("Concluido",  rs.getBoolean("Concluido"));
                for (Row row : cursor.newIterable().addMatchPattern("Cod_agenda", rs.getInt("_idAgenda"))) {
                    cursor.updateCurrentRow(table.asUpdateRow(map));
                }
                map.clear();
            }
            rs.close();
            stmt.close();
        } catch (IOException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void fillAgenda() throws SQLException {
        try {
            Table table = dbAccess.getTable("agenda_apis");
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM agenda where isNote = 1;");
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                java.util.Date dataNormal = null;
                java.util.Date dataAlerta = null;
                java.util.Date HoraAlerta = null;
                if (rs.getString("DataNormal") != null && !(rs.getString("DataNormal").isEmpty())) {
                    SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd");
                    dataNormal = (java.util.Date) dt.parse(rs.getString("DataNormal"));

                }
                if (rs.getString("DataAlerta") != null && !(rs.getString("DataAlerta").isEmpty())) {
                    SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd");
                    dataAlerta = (java.util.Date) dt.parse(rs.getString("DataAlerta"));

                }
                if (rs.getString("HoraAlerta") != null && !(rs.getString("HoraAlerta").isEmpty())) {
                    SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd");
                    HoraAlerta = (java.util.Date) dt.parse(rs.getString("HoraAlerta"));

                }
                map.put("CodigoApicultor", rs.getInt("Codigoapicultor"));
                map.put("grupoact", rs.getInt("grupoact"));
                map.put("Data", rs.getInt("Data"));
                map.put("Dia", rs.getInt("Dia"));
                map.put("Mes", rs.getInt("Mes"));
                map.put("DataNormal", dataNormal);
                map.put("Hora", rs.getString("Hora"));
                map.put("tipo_tarefa",  rs.getInt("tipo_Tarefa"));
                map.put("Tarefa", rs.getString("Tarefa"));
                map.put("cdApiario", rs.getString("cdapiario"));
                map.put("Observacoes", rs.getString("Observacoes"));
                map.put("Concluido",  rs.getBoolean("Concluido"));
                map.put("LembrarAntes1", rs.getInt("LembrarAntes1"));
                map.put("DataAlerta", dataAlerta);
                map.put("HoraAlerta", HoraAlerta);
                map.put("AlertaAutomatico", rs.getBoolean("AlertaAutomatico"));
                map.put("cod_utilizador", rs.getInt("cod_utilizador"));
                table.addRowFromMap(map);
                
            }
            rs.close();
            stmt.close();
        } catch (IOException | ParseException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void fillRegAlimentacoes() throws SQLException {
        try {
            Table table = dbAccess.getTable("reg_alimentacoes");
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM reg_alimentacoes where isNote = 1;");
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                java.util.Date data = null;
                int tipoAlimento = 1;
                int tipoAlimentacao = 1;
                if (rs.getInt("tipo_alimento") != 0) {
                    tipoAlimento = rs.getInt("tipo_alimento");
                }
                if (rs.getInt("tipo_Alimenta") != 0) {
                    tipoAlimentacao = rs.getInt("tipo_Alimenta");
                }
                if (rs.getString("data") != null && !(rs.getString("data").isEmpty())) {
                    SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd");
                    data = (java.util.Date) dt.parse(rs.getString("data"));
                }      
                
                map.put("cod_exame", rs.getInt("cod_Exame"));
                map.put("nome_enxame", rs.getString("nome_enxame"));
                map.put("Ano",  rs.getString("Ano"));
                map.put("mes",  rs.getInt("Mes"));
                map.put("data",data);
                map.put("tipo_alimento", tipoAlimento);
                map.put("tipo_alimenta", tipoAlimentacao);
                map.put("quantidade",  rs.getInt("quantidade"));
                map.put("custo_estimado", rs.getInt("custo_estimado"));
                map.put("objectivo", rs.getString("objectivo"));
                map.put("cod_apairio",rs.getInt("cod_Apairio"));
                map.put("cod_apicultor", rs.getInt("cod_Apicultor"));
                map.put("cod_utilizador",rs.getInt("cod_utilizador"));
                map.put("diasproxali", rs.getInt("diasproxali"));
                table.addRowFromMap(map);

            }
            rs.close();
            stmt.close();
        } catch (IOException | ParseException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void fillManutencoes() throws SQLException {

        try {
            Table table = dbAccess.getTable("manutencoes");
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM manutencoes where isNote = 1 and updateInsert = 0;");
            while (rs.next()) {
                java.util.Date data = null;
                if (rs.getString("data") != null && !(rs.getString("data").isEmpty())) {
                    SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd");
                    data = (java.util.Date) dt.parse(rs.getString("data"));
                }
                table.addRow(Column.AUTO_NUMBER, data, rs.getInt("cod_Apiario"), rs.getString("nome_apiario"), rs.getInt("tipodeaccao"), rs.getString("Descricao"), rs.getString("ano"),
                        rs.getInt("cod_Apicultor"), rs.getInt("cod_utilizador"));
            }
            rs.close();
            stmt.close();
        } catch (IOException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ParseException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void updateManutencoes() throws SQLException {
        Map<String, Object> map = new HashMap<>();
        try {
            Table table = dbAccess.getTable("manutencoes");
            Cursor cursor = CursorBuilder.createCursor(table);
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM manutencoes where isNote = 1 and updateInsert = 1;");
            while (rs.next()) {
                java.util.Date data = null;

                if (rs.getString("data") != null && !(rs.getString("data").isEmpty())) {
                    SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd");
                    data = (java.util.Date) dt.parse(rs.getString("data"));
                }
                map.put("ano", rs.getString("ano"));
                map.put("cod_Apiario", rs.getInt("cod_Apiario"));
                map.put("cod_Apicultor", rs.getInt("cod_Apicultor"));
                map.put("cod_utilizador", rs.getInt("cod_utilizador"));
                map.put("data", data);
                map.put("Descricao", rs.getString("Descricao"));
                map.put("nome_apiario", rs.getString("nome_apiario"));
                map.put("tipodeaccao", rs.getInt("tipodeaccao"));
                map.put("Descricao", rs.getString("Descricao"));
                for (Row row : cursor.newIterable().addMatchPattern("Cod_manu", rs.getInt("_idManutencoes"))) {
                    cursor.updateCurrentRow(table.asUpdateRow(map));
                }
                map.clear();
            }
            rs.close();
            stmt.close();
        } catch (IOException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ParseException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void fillManutencoesColm() throws SQLException {

        try {
            Table table = dbAccess.getTable("manutencoescolm");
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM manutencoescolm where isNote = 1 and updateInsert = 0;");
            while (rs.next()) {

                java.util.Date data = null;

                if (rs.getString("data") != null && !(rs.getString("data").isEmpty())) {
                    SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd");
                    data = (java.util.Date) dt.parse(rs.getString("data"));
                }
                table.addRow(Column.AUTO_NUMBER, data, rs.getString("ano"), rs.getInt("codap"), rs.getInt("codcolm"), rs.getString("numcolm"), rs.getInt("tipointrev"),
                        rs.getString("descricao"), rs.getString("temperatura"), rs.getString("humidade"), rs.getInt("doencadetct"), rs.getInt("trafego"), rs.getInt("padraopostura"),
                        rs.getInt("n_cup_reais"), rs.getInt("n_ovosdia"), rs.getInt("entrapolen"), rs.getInt("niv_reserv"));

                //manutencoes.setCodmanu(rs.getInt("_idManu"));
            }
            rs.close();
            stmt.close();
        } catch (IOException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ParseException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public void updateManutencoesColm() throws SQLException {
        Map<String, Object> map = new HashMap<>();
        try {
            Table table = dbAccess.getTable("manutencoescolm");
            Cursor cursor = CursorBuilder.createCursor(table);
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM manutencoescolm where isNote = 1 and updateInsert = 1;");
            while (rs.next()) {

                java.util.Date data = null;

                if (rs.getString("data") != null && !(rs.getString("data").isEmpty())) {
                    SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd");
                    data = (java.util.Date) dt.parse(rs.getString("data"));
                }
                map.put("data", data);
                map.put("ano", rs.getString("ano"));
                map.put("codap", rs.getInt("codap"));
                map.put("codcolm", rs.getInt("codcolm"));
                map.put("numcolm", rs.getString("numcolm"));
                map.put("tipointrev", rs.getInt("tipointrev"));
                map.put("descricao", rs.getString("descricao"));
                map.put("temperatura", rs.getString("temperatura"));
                map.put("humidade", rs.getString("humidade"));
                map.put("doencadetct", rs.getInt("doencadetct"));
                map.put("trafego", rs.getInt("trafego"));
                map.put("padraopostura", rs.getInt("padraopostura"));
                map.put("n_cup_reais", rs.getInt("n_cup_reais"));
                map.put("n_ovosdia", rs.getInt("n_ovosdia"));
                map.put("entrapolen", rs.getInt("entrapolen"));
                map.put("niv_reserv", rs.getInt("niv_reserv"));

                for (Row row : cursor.newIterable().addMatchPattern("codmanu", rs.getInt("_idManu"))) {
                    cursor.updateCurrentRow(table.asUpdateRow(map));
                }
                map.clear();
            }
            rs.close();
            stmt.close();
        } catch (IOException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ParseException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public void fillEnxame() throws SQLException {

        try {
            Table table = dbAccess.getTable("enxame");
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM exame where isNote = 1 and updateInsert = 0;");
           
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("cod_apiario", rs.getInt("cod_Apiario")); map.put("numero_colonia", rs.getString("numero_colonia"));
                map.put("cor", rs.getString("cor")); map.put("tipo_origem", rs.getInt("tipo_Origem"));
                map.put("cod_apiario_origem", rs.getInt("Cod_apiario_Origem")); map.put("colonia_origem", rs.getString("colonia_origem"));
                map.put("data_formacao", rs.getString("data_formacao")); map.put("tipo_nucleo", rs.getInt("tipo_Nucleo"));
                map.put("data_instalacao", rs.getString("data_instalacao"));
                map.put("qualidade", rs.getString("qualidade")); map.put("prob_enxemaecao", rs.getBoolean("prob_enxemaecao"));  
                map.put("possuiredutoralvado", rs.getBoolean("possuiredutoralvado")); map.put("possuitampoexcluidor", rs.getBoolean("possuitampoexcluidor"));
                map.put("captpolen", rs.getBoolean("captpolen")); map.put("quantidadealsa", rs.getBigDecimal("quantidadealsa"));
                map.put("n-alimentacoes", rs.getInt("nalimentacoes")); map.put("nivel_reservas", rs.getInt("nivel_reservas"));
                map.put("qtd_alimenta", rs.getInt("qtd_alimenta")); map.put("motivo_desact", rs.getString("motivo_desact"));
                map.put("nivel_defesa", rs.getString("nivel_defesa")); map.put("obs",  rs.getString("obs"));
                map.put("tipo_extrado", rs.getInt("tipo_extrado")); map.put("pos_x", rs.getInt("pos_x"));
                map.put("pos_y", rs.getInt("pos_y")); map.put("codabar", rs.getString("codabar"));
                map.put("etiqrdif", rs.getString("etiqrdif")); map.put("Temp_interior", rs.getBigDecimal("temp_interior"));
                map.put("humid_interior", rs.getBigDecimal("humid_interior")); map.put("Niv_ruido", rs.getBigDecimal("Niv_ruido"));
                map.put("localiza_latid", rs.getString("localiza_latid")); map.put("localiza_longitude", rs.getString("localiza_longitude"));
                map.put("desactiva", rs.getBoolean("desactiva")); map.put("grupo", rs.getInt("grupo"));
                map.put("foto", rs.getString("foto")); map.put("tabdetectvaroa", rs.getBoolean("tabdetectvaroa"));
                map.put("prod_mel", rs.getInt("prod_mel")); map.put("prod_propolis", rs.getInt("prod_propolis"));
                map.put("prod_polen", rs.getInt("prod_polen"));
                ResultSet rs2 = stmt.executeQuery("SELECT * FROM exame where numero_colonia = '" + rs.getString("colonia_origem") +"';");
                map.put("raca", rs2.getString("raca"));
                rs2.close();
               table.addRowFromMap(map);
            }
            rs.close();
            stmt.close();
        } catch (IOException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void updateEnxame() throws SQLException {
        Map<String, Object> map = new HashMap<>();
        try {
            Table table = dbAccess.getTable("enxame");
            Cursor cursor = CursorBuilder.createCursor(table);
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM exame where isNote = 1 and updateInsert = 1;");
            while (rs.next()) {

                map.put("cod_apiario", rs.getInt("cod_Apiario"));
                map.put("numero_colonia", rs.getString("numero_colonia"));
                map.put("cor", rs.getString("cor"));
                map.put("tipo_origem", rs.getInt("tipo_Origem"));
                map.put("cod_apiario_origem", rs.getInt("Cod_apiario_Origem"));
                map.put("colonia_origem", rs.getString("colonia_origem"));
                map.put("data_formacao", rs.getString("data_formacao"));
                map.put("tipo_nucleo", rs.getInt("tipo_Nucleo"));
                //map.put("raca",rs.getString("raca"));
                map.put("data_instalacao", rs.getString("data_instalacao"));
                map.put("qualidade", rs.getString("qualidade"));
                map.put("prob_enxemaecao", rs.getBoolean("prob_enxemaecao"));
                map.put("possuiredutoralvado", rs.getBoolean("possuiredutoralvado"));
                map.put("possuitampoexcluidor", rs.getBoolean("possuitampoexcluidor"));
                map.put("captpolen", rs.getBoolean("captpolen"));
                map.put("quantidadealsa", rs.getBigDecimal("quantidadealsa"));
                map.put("n-alimentacoes", rs.getInt("nalimentacoes"));
                map.put("nivel_reservas", rs.getInt("nivel_reservas"));
                map.put("qtd_alimenta", rs.getInt("qtd_alimenta"));
                map.put("motivo_desact", rs.getString("motivo_desact"));
                map.put("nivel_defesa", rs.getString("nivel_defesa"));
                map.put("obs", rs.getString("obs"));
                map.put("tipo_extrado", rs.getInt("tipo_extrado"));
                map.put("pos_x", rs.getInt("pos_x"));
                map.put("pos_y", rs.getInt("pos_y"));
                map.put("codabar", rs.getString("codabar"));
                map.put("etiqrdif", rs.getString("etiqrdif"));
                map.put("Temp_interior", rs.getBigDecimal("temp_interior"));
                map.put("humid_interior", rs.getBigDecimal("humid_interior"));
                map.put("Niv_ruido", rs.getBigDecimal("Niv_ruido"));
                map.put("localiza_latid", rs.getString("localiza_latid"));
                map.put("localiza_longitude", rs.getString("localiza_longitude"));
                map.put("desactiva", rs.getBoolean("desactiva"));
                map.put("grupo", rs.getInt("grupo"));
                map.put("foto", rs.getString("foto"));
                map.put("tabdetectvaroa", rs.getBoolean("tabdetectvaroa"));
                map.put("prod_mel", rs.getInt("prod_mel"));
                map.put("prod_propolis", rs.getInt("prod_propolis"));
                map.put("prod_polen", rs.getInt("prod_polen"));
                for (Row row : cursor.newIterable().addMatchPattern("cod_exame", rs.getInt("_idExame"))) {
                    cursor.updateCurrentRow(table.asUpdateRow(map));
                }
                map.clear();
            }
            rs.close();
            stmt.close();
        } catch (IOException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void fillRegEnxames() throws SQLException {

        try {
            Table table = dbAccess.getTable("reg_enxames");
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM reg_enxames where isNote = 1 and updateInsert = 0;");
            while (rs.next()) {

                java.util.Date data = null;
                if (rs.getString("data_enxemeacao") != null && !(rs.getString("data_enxemeacao").isEmpty())) {
                    SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd");
                    data = (java.util.Date) dt.parse(rs.getString("data_enxemeacao"));
                }
                table.addRow(Column.AUTO_NUMBER, rs.getInt("cod_Apiario"), rs.getInt("Colm_Origem"), rs.getString("ano"), data, rs.getString("Discricao"), rs.getString("Colm_originada"), rs.getInt("cod_Apicultor"),
                        rs.getInt("cod_utilizador"), rs.getInt("tipoenxame"), rs.getInt("tipodesdobra"), rs.getInt("sucesso"));

            }
            rs.close();
            stmt.close();
        } catch (IOException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ParseException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void updateRegEnxames() throws SQLException {
        Map<String, Object> map = new HashMap<>();
        try {
            Table table = dbAccess.getTable("reg_enxames");
            Cursor cursor = CursorBuilder.createCursor(table);
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM reg_enxames where isNote = 1 and updateInsert = 1;");
            while (rs.next()) {

                java.util.Date data = null;
                if (rs.getString("data_enxemeacao") != null && !(rs.getString("data_enxemeacao").isEmpty())) {
                    SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd");
                    data = (java.util.Date) dt.parse(rs.getString("data_enxemeacao"));
                }
                map.put("apiario", rs.getInt("cod_Apiario"));
                map.put("Colm_origem", rs.getInt("Colm_Origem"));
                map.put("ano", rs.getString("ano"));
                map.put("data_enxemeacao", data);
                map.put("Discricao", rs.getString("Discricao"));
                map.put("Colm_originada", rs.getString("Colm_originada"));
                map.put("cod_apicultor", rs.getInt("cod_Apicultor"));
                map.put("cod_utilizador", rs.getInt("cod_utilizador"));
                map.put("tipoenxame", rs.getInt("tipoenxame"));
                map.put("tipodesdobra", rs.getInt("tipodesdobra"));
                map.put("sucesso", rs.getInt("sucesso"));
                for (Row row : cursor.newIterable().addMatchPattern("Exam_id", rs.getInt("_idRegEnxames"))) {
                    cursor.updateCurrentRow(table.asUpdateRow(map));
                }
                map.clear();
            }
            rs.close();
            stmt.close();
        } catch (IOException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ParseException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void fillMelgueiras() throws SQLException {

        try {
            Table table = dbAccess.getTable("melgueiras");
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM melgueiras where isNote = 1 and updateInsert = 0 ;");
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                java.util.Date dataExtraccao = null;
                java.util.Date dataColocacao = null;
                if (rs.getString("data_extracacao") != null && !(rs.getString("data_extracacao").isEmpty())) {
                    SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd");
                    dataExtraccao = (java.util.Date) dt.parse(rs.getString("data_extracacao"));
                }
                if (rs.getString("data_colocacao") != null && !(rs.getString("data_colocacao").isEmpty())) {
                    SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd");
                    dataColocacao = (java.util.Date) dt.parse(rs.getString("data_colocacao"));
                }
                map.put("cod_apiario", rs.getInt("cod_Apiario"));
                map.put("cod_enxame", rs.getInt("Cod_Enxame"));
                map.put("designacao", rs.getString("designacao"));
                map.put("data_extracacao", dataExtraccao);
                map.put("data_colocacao", dataColocacao);
                map.put("tipo", rs.getInt("tipo"));
                map.put("peso", rs.getInt("peso"));
                map.put("tara", rs.getInt("tara"));
                map.put("extraida", rs.getBoolean("extraida"));
                map.put("observacoes", rs.getString("observacoes"));
                map.put("cod_apicultor", rs.getInt("cod_Apicultor"));
                map.put("cod_utilizador", rs.getInt("cod_utilizador"));
                map.put("tipoalca", rs.getInt("tipoalca"));
                table.addRowFromMap(map);
            }
            rs.close();
            stmt.close();
        } catch (IOException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ParseException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public void updateMelgueiras() throws SQLException {
        Map<String, Object> map = new HashMap<>();
        try {
            Table table = dbAccess.getTable("melgueiras");
            Cursor cursor = CursorBuilder.createCursor(table);
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM melgueiras where isNote = 1 and updateInsert = 1;");
            while (rs.next()) {

                java.util.Date dataExtraccao = null;
                java.util.Date dataColocacao = null;
                if (rs.getString("data_extracacao") != null && !(rs.getString("data_extracacao").isEmpty())) {
                    SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd");
                    dataExtraccao = (java.util.Date) dt.parse(rs.getString("data_extracacao"));
                }
                if (rs.getString("data_colocacao") != null && !(rs.getString("data_colocacao").isEmpty())) {
                    SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd");
                    dataColocacao = (java.util.Date) dt.parse(rs.getString("data_colocacao"));
                }
                map.put("cod_apiario", rs.getInt("cod_Apiario"));
                map.put("cod_enxame", rs.getInt("Cod_Enxame"));
                map.put("designacao", rs.getString("designacao"));
                map.put("data_colocacao", dataColocacao);
                map.put("data_extracacao", dataExtraccao);
                if (!(rs.getString("tipo").isEmpty())) {
                    map.put("tipo", rs.getString("tipo"));
                }
                map.put("peso", rs.getInt("peso"));
                map.put("tara", rs.getInt("tara"));
                map.put("extraida", rs.getBoolean("extraida"));
                map.put("observacoes", rs.getString("observacoes"));
                map.put("cod_apicultor", rs.getInt("cod_Apicultor"));
                map.put("cod_utilizador", rs.getInt("cod_utilizador"));
                map.put("tipoalca", rs.getInt("tipoalca"));
                for (Row row : cursor.newIterable().addMatchPattern("cod_melgueira", rs.getInt("_idMelgueira"))) {
                    cursor.updateCurrentRow(table.asUpdateRow(map));
                }
                map.clear();

            }
            rs.close();
            stmt.close();
        } catch (IOException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ParseException ex) {
            Logger.getLogger(SQLiteJDBC.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
}
